﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IMDTO;

namespace IMDAL
{
    public sealed class IMDALMetadata
    {
        public static List<FunctieRol> GetAllFunctieRollen()
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                List<FunctieRol> functieRollen = (from fr in entities.FunctieRol
                                                 select fr).ToList();
                return functieRollen;
                                                      
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public static List<IMDTORefCodeDetail> GetAllKaderItems()
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                List<IMDTORefCodeDetail> kaders = (from r in entities.RefCodeDetail
                                                   where r.idRefCode.Equals(101)
                                                   select new IMDTORefCodeDetail
                                                   {
                                                       idRefCodeDetail = r.idRefCodeDetail,
                                                       idRefCode = r.idRefCode,
                                                       refCodeDetail = r.refCodeDetail1,
                                                       Description = r.description,
                                                       ShortDescription = r.shortDescription
                                                   }).ToList();
                return kaders;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static List<IMDTORefCodeDetail> GetAllFamilieItems()
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                List<IMDTORefCodeDetail> families = (from r in entities.RefCodeDetail
                                                   where r.idRefCode.Equals(102)
                                                   select new IMDTORefCodeDetail
                                                   {
                                                       idRefCodeDetail = r.idRefCodeDetail,
                                                       idRefCode = r.idRefCode,
                                                       refCodeDetail = r.refCodeDetail1,
                                                       Description = r.description,
                                                       ShortDescription = r.shortDescription
                                                   }).ToList();
                return families;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static List<IMDTORefCodeDetail> GetAllNiveauItems()
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                List<IMDTORefCodeDetail> niveaus = (from r in entities.RefCodeDetail
                                                     where r.idRefCode.Equals(104)
                                                     select new IMDTORefCodeDetail
                                                     {
                                                         idRefCodeDetail = r.idRefCodeDetail,
                                                         idRefCode = r.idRefCode,
                                                         refCodeDetail = r.refCodeDetail1,
                                                         Description = r.description,
                                                         ShortDescription = r.shortDescription
                                                     }).ToList();
                return niveaus;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static List<IMDTORefCodeDetail> GetAllGroepItems()
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                List<IMDTORefCodeDetail> niveaus = (from r in entities.RefCodeDetail
                                                    where r.idRefCode.Equals(103)
                                                    select new IMDTORefCodeDetail
                                                    {
                                                        idRefCodeDetail = r.idRefCodeDetail,
                                                        idRefCode = r.idRefCode,
                                                        refCodeDetail = r.refCodeDetail1,
                                                        Description = r.description,
                                                        ShortDescription = r.shortDescription
                                                    }).ToList();
                return niveaus;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static int AddFunctieRol(IMDTOFunctieRol rol)
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                FunctieRol fr = new FunctieRol();
                fr.Naam = rol.Name;
                fr.Kader = rol.KaderRefId;
                fr.FunctieFamilie = rol.FamilieRefId;
                fr.FunctieGroep = rol.GroepRefId;
                fr.FunctieNiveau = rol.NiveauRefId;
                entities.FunctieRol.AddObject(fr);
                entities.SaveChanges();

                return fr.IdFunctieRol;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static void DeleteFunctieRol(int idFunctieRol)
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                FunctieRol fr = (from f in entities.FunctieRol
                                where f.IdFunctieRol.Equals(idFunctieRol)
                                select f).Single();
                entities.FunctieRol.DeleteObject(fr);

                entities.SaveChanges();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static void UpdateFunctieRol(IMDTOFunctieRol rol)
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                FunctieRol fr = (from f in entities.FunctieRol
                                 where f.IdFunctieRol.Equals(rol.idFunctieRol)
                                 select f).Single();

                if (!(rol.KaderRefId == 0)) fr.Kader = rol.KaderRefId;
                if (!(rol.FamilieRefId == 0)) fr.FunctieFamilie = rol.FamilieRefId;
                if (!(rol.NiveauRefId == 0)) fr.FunctieNiveau = rol.NiveauRefId;
                if (!(rol.GroepRefId == 0)) fr.FunctieGroep = rol.GroepRefId;
                if (!(string.IsNullOrWhiteSpace(rol.Name))) fr.Naam = rol.Name;

                
                entities.SaveChanges();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static int AddRefCodeDetail(IMDTORefCodeDetail refDetail)
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                int maxId = (from r in entities.RefCodeDetail
                             where r.idRefCode.Equals(refDetail.idRefCode)
                             select r.refCodeDetail1).Max();

                RefCodeDetail rcd = new RefCodeDetail();
                rcd.idRefCode = refDetail.idRefCode;
                rcd.refCodeDetail1 = maxId + 1;
                rcd.description = refDetail.Description;
                rcd.shortDescription = refDetail.ShortDescription;
                entities.RefCodeDetail.AddObject(rcd);
                entities.SaveChanges();

                //int insertedId = (from r in entities.RefCodeDetail
                //                  where r.idRefCode.Equals(refDetail.idRefCode) &&
                //                  r.refCodeDetail1.Equals(rcd.refCodeDetail1)
                //                  select r.idRefCodeDetail).Single();

                return rcd.idRefCodeDetail;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static void UpdateRefCodeDetail(IMDTORefCodeDetail refDetail)
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                RefCodeDetail rcd = (from r in entities.RefCodeDetail
                                 where r.idRefCodeDetail.Equals(refDetail.idRefCodeDetail)
                                 select r).Single();

                if (!(string.IsNullOrWhiteSpace(refDetail.Description))) rcd.description = refDetail.Description;
                if (!(string.IsNullOrWhiteSpace(refDetail.ShortDescription))) rcd.shortDescription = refDetail.ShortDescription;


                entities.SaveChanges();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static void DeleteRefCodeDetail(int idRefCodeDetail)
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                RefCodeDetail rcd = (from r in entities.RefCodeDetail
                                     where r.idRefCodeDetail.Equals(idRefCodeDetail)
                                     select r).Single() ;
                entities.RefCodeDetail.DeleteObject(rcd);

                entities.SaveChanges();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static List<AanvullendeFunctieRol> GetAllAanvullendeFunctieRollen()
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                List<AanvullendeFunctieRol> functieRollen = (from afr in entities.AanvullendeFunctieRol
                                                  select afr).ToList();
                return functieRollen;

            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static List<IMDTORefCodeDetail> GetAllAanvullendeFamilieItems()
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                List<IMDTORefCodeDetail> families = (from r in entities.RefCodeDetail
                                                     where r.idRefCode.Equals(112)
                                                     select new IMDTORefCodeDetail
                                                     {
                                                         idRefCodeDetail = r.idRefCodeDetail,
                                                         idRefCode = r.idRefCode,
                                                         refCodeDetail = r.refCodeDetail1,
                                                         Description = r.description,
                                                         ShortDescription = r.shortDescription
                                                     }).ToList();
                return families;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static int AddAanvullendeFunctieRol(IMDTOAanvullendeFunctieRol rol)
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                AanvullendeFunctieRol afr = new AanvullendeFunctieRol();
                afr.Description = rol.Description;
                afr.FunctieFamilie = rol.FamilieRefId;
                afr.FunctieNiveau = rol.NiveauRefId;
                entities.AanvullendeFunctieRol.AddObject(afr);
                entities.SaveChanges();

                return afr.IdAanvullendeFunctieRol;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static void DeleteAanvullendeFunctieRol(int idAanvullendeFunctieRol)
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                AanvullendeFunctieRol afr = (from f in entities.AanvullendeFunctieRol
                                where f.IdAanvullendeFunctieRol.Equals(idAanvullendeFunctieRol)
                                select f).Single();
                entities.AanvullendeFunctieRol.DeleteObject(afr);

                entities.SaveChanges();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static void UpdateAanvullendeFunctieRol(IMDTOAanvullendeFunctieRol rol)
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                AanvullendeFunctieRol fr = (from f in entities.AanvullendeFunctieRol
                                 where f.IdAanvullendeFunctieRol.Equals(rol.idAanvullendeFunctieRol)
                                 select f).Single();

                if (!(rol.FamilieRefId == 0)) fr.FunctieFamilie = rol.FamilieRefId;
                if (!(rol.NiveauRefId == 0)) fr.FunctieNiveau = rol.NiveauRefId;
                if (!(string.IsNullOrWhiteSpace(rol.Description))) fr.Description = rol.Description;


                entities.SaveChanges();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static List<IMDTORefCodeDetail> GetReferences(int refCodeDetail)
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                List<IMDTORefCodeDetail> references = (from r in entities.RefCodeDetail
                                                    where r.idRefCode.Equals(refCodeDetail)
                                                    select new IMDTORefCodeDetail
                                                    {
                                                        idRefCodeDetail = r.idRefCodeDetail,
                                                        idRefCode = r.idRefCode,
                                                        refCodeDetail = r.refCodeDetail1,
                                                        Description = r.description,
                                                        ShortDescription = r.shortDescription
                                                    }).ToList();
                return references;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static List<IMDTOCompetentie> GetCompetenties(int type)
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                List<IMDTOCompetentie> competenties = (from c in entities.Competentie
                                                       where c.Type.Equals(type)
                                                       select new IMDTOCompetentie
                                                       {
                                                           idCompetentie = c.idCompetentie,
                                                           Type = c.Type,
                                                           Cluster = c.Cluster,
                                                           Competentie = c.Competentie1,
                                                           Niveau = c.Niveau
                                                       }).ToList();
                return competenties;

            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static int AddCompetentie(IMDTOCompetentie cmp)
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                Competentie c = new Competentie();
                c.Type = cmp.Type;
                c.Cluster = cmp.Cluster;
                c.Competentie1 = cmp.Competentie;
                c.Niveau = cmp.Niveau;
                entities.Competentie.AddObject(c);
                entities.SaveChanges();

                return c.idCompetentie;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static void DeleteCompetentie(int idCompetentie)
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                Competentie cmp = (from c in entities.Competentie
                                             where c.idCompetentie.Equals(idCompetentie)
                                             select c).Single();
                entities.Competentie.DeleteObject(cmp);

                entities.SaveChanges();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static void UpdateCompetentie(IMDTOCompetentie cmp)
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                Competentie competentie = (from c in entities.Competentie
                                            where c.idCompetentie.Equals(cmp.idCompetentie)
                                            select c).Single();

                if (!(cmp.Cluster == 0)) competentie.Cluster = cmp.Cluster;
                if (!(cmp.Competentie == 0)) competentie.Competentie1 = cmp.Competentie;
                competentie.Niveau = cmp.Niveau;


                entities.SaveChanges();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static List<IMDTORefCodeDetail> GetGidsenForTechnischeCompetentie(int tcId)
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                List<int> gidsIds = (from g in entities.CompetentieGids
                                     where g.idCompetentie.Equals(tcId)
                                     select g.Compentiegids).ToList();
                List<IMDTORefCodeDetail> gidsen = (from g in entities.RefCodeDetail
                                                   where g.idRefCode.Equals(116) &&
                                                   gidsIds.Contains(g.refCodeDetail1)
                                                   select new IMDTORefCodeDetail
                                                   {
                                                       idRefCodeDetail = g.idRefCodeDetail,
                                                       idRefCode = g.idRefCode,
                                                       refCodeDetail = g.refCodeDetail1,
                                                       Description = g.description,
                                                       ShortDescription = g.shortDescription
                                                   }).ToList();
                return gidsen;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static void AddGidsToCompetentie(int idGids, int idCompetentie)
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                CompetentieGids cg = new CompetentieGids();
                cg.Compentiegids = idGids;
                cg.idCompetentie = idCompetentie;
                cg.UserCrea = "WV";
                cg.TsCrea = new DateTime();

                entities.CompetentieGids.AddObject(cg);

                entities.SaveChanges();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static void RemoveGidsFromCompetentie(int idGids, int idCompetentie)
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                CompetentieGids cg = (from c in entities.CompetentieGids
                                      where c.idCompetentie.Equals(idCompetentie) &&
                                      c.Compentiegids.Equals(idGids)
                                      select c).First();

                entities.CompetentieGids.DeleteObject(cg);

                entities.SaveChanges();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static List<IMDTOKernResultaat> GetAllKernresultaten()
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                List<IMDTOKernResultaat> resultaten = (from kr in entities.KernResultaat
                                                       select new IMDTOKernResultaat
                                                       {
                                                           IdKernresultaat = kr.IdKernresultaat,
                                                           ResultaatRefId = kr.Kernresultaat1,
                                                           GebiedRefId = kr.KernresultaatGebied,
                                                           TaakRefId = kr.KernTaak
                                                       }).ToList();
                return resultaten;

            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static int AddKernresultaat(IMDTOKernResultaat kr)
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                KernResultaat k = new KernResultaat();
                k.Kernresultaat1 = kr.ResultaatRefId;
                k.KernresultaatGebied = kr.GebiedRefId;
                k.KernTaak = kr.TaakRefId;
                entities.KernResultaat.AddObject(k);
                entities.SaveChanges();

                return k.IdKernresultaat;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static void DeleteKernresultaat(int idKernresultaat)
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                KernResultaat kr = (from k in entities.KernResultaat
                                   where k.IdKernresultaat.Equals(idKernresultaat)
                                   select k).Single();
                entities.KernResultaat.DeleteObject(kr);

                entities.SaveChanges();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static void UpdateKernresultaat(IMDTOKernResultaat kr)
        {
            try
            {
                InMedioEntities entities = new InMedioEntities();
                KernResultaat resultaat = (from k in entities.KernResultaat
                                           where k.IdKernresultaat.Equals(kr.IdKernresultaat)
                                           select k).Single();

                if (!(kr.ResultaatRefId == 0)) resultaat.Kernresultaat1 = kr.ResultaatRefId;
                if (!(kr.GebiedRefId == 0)) resultaat.KernresultaatGebied = kr.GebiedRefId;
                if (!(kr.TaakRefId == 0)) resultaat.KernTaak = kr.TaakRefId;


                entities.SaveChanges();
            }
            catch (Exception e)
            {
                throw e;
            }
        }
    }
}
